package com.my.xq.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

import com.my.xq.domain.CourtRoleBindQueryParam;
import com.my.xq.domain.entity.CourtRole_Proprietor;
import com.sskj.core.query.annotation.DynamicQuery;
import com.sskj.core.repository.GenericRepository;

public interface CourtRoleBindRepository extends GenericRepository<CourtRole_Proprietor, String> {
	@DynamicQuery
	Page<CourtRole_Proprietor> findPage(CourtRoleBindQueryParam cbq, Pageable pageable);

	@Query("SELECT r FROM CourtRole_Proprietor r " + " where r.doorPlateId =?1 AND r.roleId=?2")
	CourtRole_Proprietor findCourtRoleByName(String doorPlateId, String roleId);

	@Query("SELECT r FROM CourtRole_Proprietor r " + " where r.doorPlateId in("
			+ "SELECT x.id FROM Doorplate x where x.courtId=?1)")
	List<CourtRole_Proprietor> searchywhGroup(String courtId);

	@Query(value = "SELECT r FROM CourtRole_Proprietor r where r.roleId=?2 and r.doorPlateId in (select d.id from Doorplate d where d.courtId=?1)")
	List<CourtRole_Proprietor> getCortRoleByCourtRoleId(String courtId, String roleId);

	@Query(value = "SELECT r FROM CourtRole_Proprietor r where r.roleId=?2 and r.courtId =?1 and r.doorPlateId not in (select r2.doorPlateId from CourtRole_Proprietor r2 where r.courtId =?1 and r2.roleName = '主任') ")
	List<CourtRole_Proprietor> getCortRoleByCourtRoleIdExceptZr(String courtId, String roleId);

	@Query(value = "select * from xq_courtrole_proprietor rp "
			+ " where "
			+ "   rp.door_plate_id in (select d.id from xq_doorplate d WHERE d.court_id = ?1 and d.proprietor_id = ?2) "
			+ " and "
			+ "   rp.role_id in (select id from xq_courtrole r where r.role_weight > 300 and r.role_weight < 400) "
			+ " LIMIT 1 ", nativeQuery = true)
	CourtRole_Proprietor getJobByPidAndCid(String cid, String pid);

	@Query(value = "select * from xq_courtrole_proprietor rp"
			+ " where "
			+ "   rp.role_id in (select r.id from xq_courtrole r where r.role_name = ?3) "
			+ " and "
			+ "   rp.door_plate_id in ("
			+ "     select "
			+ "       d.id "
			+ "     from "
			+ "       xq_doorplate d "
			+ "     where "
			+ "       d.proprietor_id = ?2 "
			+ "       and d.court_id = ?1);", nativeQuery = true)
	List<CourtRole_Proprietor> getRole(String cid, String pid, String role);
}
